﻿Imports System.Data.SqlClient

Public Class Adaptador

#Region "Miembros/Propiedades"
    'Private Const MyConnectionString As String = "Server=COLOSO;" _
    '            & "Database=moises2011;User ID=sa;Password=2978;" _
    Private Const MyConnectionString As String = "Server=localhost;" _
                    & "Database=EDUCACION_FISICA;User ID=sa;Password=2978;" _
                    & "Pooling=true;" _
                    & "Min Pool Size=0;" _
                    & "Max Pool Size=100;" _
                    & "Connection Lifetime=0"
    Protected myDataSet As DataSet
    Protected conn As SqlConnection
    Protected comando As SqlCommand
    Protected myAdapter As SqlDataAdapter
    Protected myReader As SqlDataReader
    Protected Event ErrorBaseServer(ByVal Descripcion As String)
#End Region

#Region "Conecta y Desconecta"

    Protected Function Conectar()
        Try
            conn = New SqlConnection
            comando = New SqlCommand
            myDataSet = New DataSet
            myAdapter = New SqlDataAdapter
            conn.ConnectionString = MyConnectionString
            conn.Open()
        Catch ex As Exception
            RaiseEvent ErrorBaseServer(ex.Message)
        End Try
    End Function

    Protected Function Desconectar()
        Try
            comando.Dispose()
            myAdapter.Dispose()
            conn.Close()
        Catch ex As Exception
            RaiseEvent ErrorBaseServer(ex.Message)
        End Try
    End Function

    Public Function ConnectionString() As String
        Try
            myAdapter = New SqlDataAdapter
            Conectar()
            Desconectar()
            Return MyConnectionString
        Catch ex As Exception
            RaiseEvent ErrorBaseServer(ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' Genera un datatable a partir de un DataReader(una lectura de comando sql)
    ''' </summary>
    ''' <param name="_reader"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetTable(ByVal _reader As SqlDataReader) As DataTable
        Dim _table As System.Data.DataTable = _reader.GetSchemaTable()
        Dim _dt As New System.Data.DataTable("MXML")
        Dim _dc As System.Data.DataColumn
        Dim _row As System.Data.DataRow
        Dim _al As New System.Collections.ArrayList()
        Try
            For i As Integer = 0 To _table.Rows.Count - 1

                _dc = New System.Data.DataColumn()

                If Not _dt.Columns.Contains(_table.Rows(i)("ColumnName").ToString()) Then
                    _dc.ColumnName = _table.Rows(i)("ColumnName").ToString()
                    _dc.Unique = Convert.ToBoolean(_table.Rows(i)("IsUnique"))
                    _dc.AllowDBNull = Convert.ToBoolean(_table.Rows(i)("AllowDBNull"))
                    _dc.[ReadOnly] = Convert.ToBoolean(_table.Rows(i)("IsReadOnly"))
                    _al.Add(_dc.ColumnName)

                    _dt.Columns.Add(_dc)
                End If
            Next

            While _reader.Read()

                _row = _dt.NewRow()
                For i As Integer = 0 To _al.Count - 1
                    _row(DirectCast(_al(i), String)) = _reader(DirectCast(_al(i), String))
                Next

                _dt.Rows.Add(_row)
            End While

            Return _dt
        Catch ex As Exception
            RaiseEvent ErrorBaseServer("Adaptador: " & ex.Message)
        End Try

    End Function

#End Region

End Class